package com.witframework.core.persistence;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.simple.ParameterizedRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.transaction.annotation.Transactional;

import com.witframework.WitException;
import com.witframework.core.ApplicationProperties;
import com.witframework.dynoengine.Converter;
import com.witframework.util.BeanUtils;
import com.witframework.util.PrintObject;


/**
 *
 * JdbcTemplate DAO 实现sql最好只是用来复杂查询而且单独事务
 */
@Transactional
@SuppressWarnings("unchecked")
public class BaseJdbcDao  extends SimpleJdbcDaoSupport implements IBaseJdbcDao{
	
	private String database;

	private String[] cacheTableClass=null;
	private String hasCache(String table){
		if(cacheTableClass==null){
			String cacheC=ApplicationProperties.getProperty("hibernate.cache.db","");
			cacheTableClass=StringUtils.split(cacheC,",");
		}
		if(cacheTableClass!=null){
			for(String t:cacheTableClass){
				if(t.indexOf(table+":")>-1){
					return StringUtils.split(t,":")[1];
				}
			}
		}
		return null;
	}

	/**
	 * 插入数据
	 */
	public void insert(String tableName,Map<String,Object> data){		
		SimpleJdbcInsert insertActor=new SimpleJdbcInsert(super.getDataSource()).withTableName(tableName);	
		insertActor.execute(data);			
	}
	public void insertByWebMap(String tableName,Map<String,String> data){
		Connection conn=null;
		Map<String,Object> newData=new HashMap();
		try {
			conn = getDataSource().getConnection();
			ResultSetMetaData meta = getResultSetMetaData(conn,tableName);			
			for(int i=1;i<=meta.getColumnCount();i++){
				String columnName=meta.getColumnName(i).toUpperCase();				
				int columnType=meta.getColumnType(i);
				newData.put(columnName, Converter.convertToObject(columnType, (String)data.get(columnName)) );
			}
		} catch (SQLException e) {		
			throw new WitException(e);
		}finally{
			DBUtil.closeObject(conn);
		}
		insert(tableName,newData);			
	}
	/**
	 * 更新数据
	 * @param tableName
	 * @param data
	 * @param id
	 */
	public void update(String tableName,Map<String,Object> data){
		List<Map> primaryKeys=getTableMetaData(tableName,"PKS");
		 String sql="";
		 for(Iterator itor=data.keySet().iterator();itor.hasNext();){	
			 String columnName=(String)itor.next();
			 if(!isPk(primaryKeys,columnName)){//主键字段不能在set字句出现
				 if(sql.length()>0) sql+=",";
				 sql+=columnName+"=:"+columnName;				
			 }
		 }
		 sql="UPDATE "+tableName+"  SET "+sql+" WHERE ";
		 List<Map> pkMaps=getTableMetaData(tableName,"PKS");
		 for(int i=0;i<pkMaps.size();i++){
			 if(i>0) sql+=" AND ";
			 String columnName=(String)pkMaps.get(i).get("COLUMN_NAME");
			 columnName=columnName.toUpperCase();
			 sql+=columnName+"=:"+columnName;			
		 }
		 System.out.println("BaseJdbc.sql="+sql);
		 System.out.println("BaseJdbc.map="+PrintObject.printMap(data));
		 getSimpleJdbcTemplate().update(sql, data);
	}
	private boolean isPk(List<Map> primaryKeys,String columnName){		
		for(Map m:primaryKeys){
			String cn=(String)m.get("COLUMN_NAME");
			if(cn.equalsIgnoreCase(columnName))
				return true;
		}
		return false;
	}
	
	public void updateByWebMap(String tableName,Map<String,String> data){
		Connection conn=null;
		System.out.println("map="+PrintObject.printMap(data));
		Map<String,Object> newData=new HashMap();
		try {
			conn = getDataSource().getConnection();
			ResultSetMetaData meta = getResultSetMetaData(conn,tableName);
			for(int i=1;i<=meta.getColumnCount();i++){
				String columnName=meta.getColumnName(i).toUpperCase();
				int columnType=meta.getColumnType(i);
				newData.put(columnName, Converter.convertToObject(columnType, (String)data.get(columnName)) );
			}
		} catch (SQLException e) {		
			throw new WitException(e);
		}finally{
			DBUtil.closeObject(conn);
		}	
		update(tableName, newData);
	}
	public void delete(String tableName,Map<String,Object> data){
		List<Map> primaryKeys=getTableMetaData(tableName,"PKS");
		String sql="";		
		 sql="DELETE FROM "+tableName+" WHERE ";
		 List<Map> pkMaps=getTableMetaData(tableName,"PKS");
		 for(int i=0;i<pkMaps.size();i++){
			 if(i>0) sql+=" AND ";
			 String columnName=(String)pkMaps.get(i).get("COLUMN_NAME");
			 columnName=columnName.toUpperCase();
			 sql+=columnName+"=:"+columnName;			
		 }
		 System.out.println("BaseJdbc.sql="+sql);
		 System.out.println("BaseJdbc.map="+PrintObject.printMap(data));
		 getSimpleJdbcTemplate().update(sql, data);
	}
	public void deleteByWebMap(String tableName,Map<String,String> data){	
		Connection conn=null;
		System.out.println("map="+PrintObject.printMap(data));
		Map<String,Object> newData=new HashMap();
		try {
			conn = getDataSource().getConnection();
			ResultSetMetaData meta = getResultSetMetaData(conn,tableName);			
			for(int i=1;i<=meta.getColumnCount();i++){
				String columnName=meta.getColumnName(i).toUpperCase();
				int columnType=meta.getColumnType(i);
				newData.put(columnName, Converter.convertToObject(columnType, (String)data.get(columnName)) );
			}
		} catch (SQLException e) {		
			throw new WitException(e);
		}finally{
			DBUtil.closeObject(conn);
		}	
		delete(tableName, newData);
	}
	
	private ResultSetMetaData getResultSetMetaData(Connection conn,String tableName)
			throws SQLException {		
		try {
			
			Statement stmt = conn.createStatement();			
			ResultSet rs = stmt.executeQuery("SELECT * FROM " + tableName);			
			ResultSetMetaData rsmd = rs.getMetaData();
			return rsmd;
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		}
	}
	/**
	 * 
	 * @param tableName
	 * @param type PKS:getPrimaryKeys;IKS:getImportedKeys;EKS:getExportedKeys
	 * @return
	 */
	public List<Map> getTableMetaData(String tableName,String type){
		Connection conn = null;
		try {
			conn =getDataSource().getConnection();
			DatabaseMetaData meta=conn.getMetaData();
			if(type.equalsIgnoreCase("PKS")){
				ResultSet rs= meta.getPrimaryKeys(null, null, tableName);
				return rs2MapList(rs);
			}
			else if(type.equalsIgnoreCase("IKS")){
				ResultSet rs= meta.getImportedKeys(null, null, tableName);
				return rs2MapList(rs);
			}else if(type.equalsIgnoreCase("EKS")){
				ResultSet rs= meta.getExportedKeys(null, null, tableName);
				return rs2MapList(rs);
			}else{
				return null;
			}
		}catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBUtil.closeObject(conn);
		}
	}
	
	public static List<Map> rs2MapList(ResultSet rs) throws SQLException {
		List result=new ArrayList();
		if (null == rs) {
			return null;
		}
		while(rs.next()){
			Map<String, Object> map = new HashMap<String, Object>();
			ResultSetMetaData meta = rs.getMetaData();
			int count = meta.getColumnCount();
			for (int i = 0; i < count; i++) {
				map.put(meta.getColumnName(i + 1), rs.getObject(i + 1));
			}
			result.add(map);
		}
		return result;
	}
	public void deleteBy(String tableName,String key,String vlaue){
		super.getSimpleJdbcTemplate().update("delete from "+tableName+" where "+key+"=?", vlaue);
	}	
	
	/* (non-Javadoc)
	 * @see com.witframework.core.persistence.IBaseJdbcDao#queryForObject(java.lang.String, org.springframework.jdbc.core.simple.ParameterizedRowMapper, java.lang.Object)
	 */
	public Object queryForObject(String sql,ParameterizedRowMapper<?> mapper,Object...args){
		try{
		return getSimpleJdbcTemplate().queryForObject(sql, mapper, args);
		}catch(org.springframework.dao.EmptyResultDataAccessException ex){
			return null;
		}
	}
	/* (non-Javadoc)
	 * @see com.witframework.core.persistence.IBaseJdbcDao#queryForObject(java.lang.String, java.lang.Class, java.lang.Object)
	 */
	public <T> T queryForObject(String sql,Class<T> entityClass,Object...args) {
		return (T) queryForObject(sql,new SimpleParameterizedRowMapper(sql,entityClass), args);

	}
	public Map queryForMap(String sql,Object...args) {
		return (Map)queryForObject(sql,new SimpleParameterizedRowToMapsMapper(sql), args);

	}
	/* (non-Javadoc)
	 * @see com.witframework.core.persistence.IBaseJdbcDao#queryForList(java.lang.String, java.lang.Object)
	 */
	public List<?> queryForList(String sql,Object...args){
		return getSimpleJdbcTemplate().query(sql,new SimpleParameterizedRowToMapsMapper(sql), args);
	}
	/* (non-Javadoc)
	 * @see com.witframework.core.persistence.IBaseJdbcDao#queryForList(java.lang.String, org.springframework.jdbc.core.simple.ParameterizedRowMapper, java.lang.Object)
	 */

	public List<?> queryForList(String sql,ParameterizedRowMapper mapper,Object...args){
		return getSimpleJdbcTemplate().query(sql, mapper, args);

	}
	/* (non-Javadoc)
	 * @see com.witframework.core.persistence.IBaseJdbcDao#queryForList(java.lang.String, java.lang.Class, java.lang.Object)
	 */
	public List queryForList(String sql,Class entityClass,Object...args){
		return queryForList(sql,new SimpleParameterizedRowMapper(sql,entityClass),args);
	}
	/* (non-Javadoc)
	 * @see com.witframework.core.persistence.IBaseJdbcDao#pageQuery(com.witframework.core.persistence.PageSortModel, java.lang.String, org.springframework.jdbc.core.simple.ParameterizedRowMapper, java.lang.Object)
	 */
	public List pageQuery(PageSortModel psm,String sql,ParameterizedRowMapper mapper,Object...args){
		if(database.equalsIgnoreCase("oracle"))
			return pageQuery4Oracle(psm,sql,mapper,args);
		else if(database.equalsIgnoreCase("mysql"))
			return pageQuery4MySql(psm,sql,mapper,args);
		else if(database.equalsIgnoreCase("hsql"))
			return pageQuery4HSql(psm,sql,mapper,args);
		else if(database.equalsIgnoreCase("sqlserver"))
			return pageQuery4Sqlserver(psm,sql,mapper,args);
		else
			return pageQuery4Other(psm,sql,mapper,args);
			//throw new WitFrameWorkException("不支持的数据库类型："+database);
	}
	/* (non-Javadoc)
	 * @see com.witframework.core.persistence.IBaseJdbcDao#pageQuery4Oracle(com.witframework.core.persistence.PageSortModel, java.lang.String, org.springframework.jdbc.core.simple.ParameterizedRowMapper, java.lang.Object)
	 */
	public List pageQuery4Oracle(PageSortModel psm,String sql,ParameterizedRowMapper mapper,Object...args){
		
		int totalRow=super.getSimpleJdbcTemplate().queryForInt("select count(1) from("+sql+") tmptb", args);
		psm.setTotalRows(totalRow);
		sql=getLimitString4Oracle(sql,psm.getStartOfPage(),psm.getStartOfPage()+psm.getPageSize()-1);
		sql=addOrder(sql,psm.getOrderMap());
		if(Boolean.parseBoolean(ApplicationProperties.getProperty("hibernate.show_sql","false")))
			System.out.println("JdbcDao:"+sql);
		return getSimpleJdbcTemplate().query(sql, mapper, args);

	}
	
	/* (non-Javadoc)
	 * @see com.witframework.core.persistence.IBaseJdbcDao#pageQuery4MySql(com.witframework.core.persistence.PageSortModel, java.lang.String, org.springframework.jdbc.core.simple.ParameterizedRowMapper, java.lang.Object)
	 */
	public List pageQuery4MySql(PageSortModel psm,String sql,ParameterizedRowMapper mapper,Object...args){
		int totalRow=super.getSimpleJdbcTemplate().queryForInt("select count(1) from("+sql+") tmptb", args);
		psm.setTotalRows(totalRow);
		sql=getLimitString4MySql(sql,psm.getStartOfPage(),psm.getStartOfPage()+psm.getPageSize()-1);
		sql=addOrder(sql,psm.getOrderMap());
		if(Boolean.parseBoolean(ApplicationProperties.getProperty("hibernate.show_sql","false")))
			System.out.println("JdbcDao:"+sql);
		return getSimpleJdbcTemplate().query(sql, mapper, args);

	}
	/* (non-Javadoc)
	 * @see com.witframework.core.persistence.IBaseJdbcDao#pageQuery4HSql(com.witframework.core.persistence.PageSortModel, java.lang.String, org.springframework.jdbc.core.simple.ParameterizedRowMapper, java.lang.Object)
	 */
	public List pageQuery4HSql(PageSortModel psm,String sql,ParameterizedRowMapper mapper,Object...args){
		int totalRow=super.getSimpleJdbcTemplate().queryForInt("select count(1) from("+sql+") tmptb", args);
		psm.setTotalRows(totalRow);
		sql=getLimitString4HSql(sql,psm.getStartOfPage(),psm.getStartOfPage()+psm.getPageSize()-1);
		sql=addOrder(sql,psm.getOrderMap());
		if(Boolean.parseBoolean(ApplicationProperties.getProperty("hibernate.show_sql","false")))
			System.out.println("JdbcDao:"+sql);
		return getSimpleJdbcTemplate().query(sql, mapper, args);

	}
	public List pageQuery4Sqlserver(PageSortModel psm,String sql,ParameterizedRowMapper mapper,Object...args){
		int totalRow=super.getSimpleJdbcTemplate().queryForInt("select count(1) from("+sql+") tmptb", args);
		psm.setTotalRows(totalRow);
		sql=getLimitString4SqlServer(sql,psm.getStartOfPage(),psm.getStartOfPage()+psm.getPageSize()-1);
		sql=addOrder(sql,psm.getOrderMap());
		if(Boolean.parseBoolean(ApplicationProperties.getProperty("hibernate.show_sql","false")))
			System.out.println("JdbcDao:"+sql);
		return getSimpleJdbcTemplate().query(sql, mapper, args);

	}
	/* (non-Javadoc)
	 * @see com.witframework.core.persistence.IBaseJdbcDao#pageQuery4Other(com.witframework.core.persistence.PageSortModel, java.lang.String, org.springframework.jdbc.core.simple.ParameterizedRowMapper, java.lang.Object)
	 */
	public List pageQuery4Other(PageSortModel psm,String sql,ParameterizedRowMapper mapper,Object...args){
		int totalRow=super.getSimpleJdbcTemplate().queryForInt("select count(1) from("+sql+") tmptb", args);
		psm.setTotalRows(totalRow);
		sql=getLimitString4Other(sql,psm.getStartOfPage(),psm.getStartOfPage()+psm.getPageSize()-1);
		sql=addOrder(sql,psm.getOrderMap());
		if(Boolean.parseBoolean(ApplicationProperties.getProperty("hibernate.show_sql","false")))
			System.out.println("JdbcDao:"+sql);
		return getSimpleJdbcTemplate().query(sql, mapper, args);

	}
	
	
	/* (non-Javadoc)
	 * @see com.witframework.core.persistence.IBaseJdbcDao#pageQuery(com.witframework.core.persistence.PageSortModel, java.lang.String, java.lang.Object)
	 */
	public List pageQuery(PageSortModel psm,String sql,Object...args){
		if(database.equalsIgnoreCase("oracle"))
			return pageQuery4Oracle(psm,sql,new SimpleParameterizedRowToMapsMapper(sql),args);
		else if(database.equalsIgnoreCase("mysql"))
			return pageQuery4MySql(psm,sql,new SimpleParameterizedRowToMapsMapper(sql),args);
		else if(database.equalsIgnoreCase("hsql"))
			return pageQuery4HSql(psm,sql,new SimpleParameterizedRowToMapsMapper(sql),args);
		else if(database.equalsIgnoreCase("sqlserver"))
			return pageQuery4Sqlserver(psm,sql,new SimpleParameterizedRowToMapsMapper(sql),args);
		else
			return this.pageQuery4Other(psm,sql,new SimpleParameterizedRowToMapsMapper(sql),args);			
	}
	/* (non-Javadoc)
	 * @see com.witframework.core.persistence.IBaseJdbcDao#pageQuery(com.witframework.core.persistence.PageSortModel, java.lang.String, java.lang.Class, java.lang.Object)
	 */
	public List pageQuery(PageSortModel psm,String sql,Class entityClass,Object...args){
		if(database.equalsIgnoreCase("oracle"))
			return pageQuery4Oracle(psm,sql,entityClass,args);
		else if(database.equalsIgnoreCase("mysql"))
			return pageQuery4MySql(psm,sql,entityClass,args);
		else if(database.equalsIgnoreCase("hsql"))
			return pageQuery4HSql(psm,sql,entityClass,args);
		else if(database.equalsIgnoreCase("sqlserver"))
			return pageQuery4Sqlserver(psm,sql,entityClass,args);
		else
			throw new WitException("不支持的数据库类型："+database);
	}
	/* (non-Javadoc)
	 * @see com.witframework.core.persistence.IBaseJdbcDao#pageQuery4Oracle(com.witframework.core.persistence.PageSortModel, java.lang.String, java.lang.Class, java.lang.Object)
	 */
	public List pageQuery4Oracle(PageSortModel psm,String sql,Class entityClass,Object...args){
		return pageQuery4Oracle(psm,sql,new SimpleParameterizedRowMapper(sql,entityClass),args);
	}
	/* (non-Javadoc)
	 * @see com.witframework.core.persistence.IBaseJdbcDao#pageQuery4MySql(com.witframework.core.persistence.PageSortModel, java.lang.String, java.lang.Class, java.lang.Object)
	 */
	public List pageQuery4MySql(PageSortModel psm,String sql,Class entityClass,Object...args){
		return pageQuery4MySql(psm,sql,new SimpleParameterizedRowMapper(sql,entityClass),args);
	}
	/* (non-Javadoc)
	 * @see com.witframework.core.persistence.IBaseJdbcDao#pageQuery4HSql(com.witframework.core.persistence.PageSortModel, java.lang.String, java.lang.Class, java.lang.Object)
	 */
	public List pageQuery4HSql(PageSortModel psm,String sql,Class entityClass,Object...args){
		return pageQuery4HSql(psm,sql,new SimpleParameterizedRowMapper(sql,entityClass),args);
	}
	public List pageQuery4Sqlserver(PageSortModel psm,String sql,Class entityClass,Object...args){
		return pageQuery4Sqlserver(psm,sql,new SimpleParameterizedRowMapper(sql,entityClass),args);
	}
	/* (non-Javadoc)
	 * @see com.witframework.core.persistence.IBaseJdbcDao#pageQuery4Other(com.witframework.core.persistence.PageSortModel, java.lang.String, java.lang.Class, java.lang.Object)
	 */
	public List pageQuery4Other(PageSortModel psm,String sql,Class entityClass,Object...args){
		return pageQuery4Other(psm,sql,new SimpleParameterizedRowMapper(sql,entityClass),args);
	}
	/*
	 * 组装分页查询语句，该方法只适用于oracle
	 * @param sql - 原SQL语句
	 * @param start - 开始记录数
	 * @param end - 结束记录数
	 * @return - 组装后的分页SQL语句
	 */
	private String getLimitString4Oracle(String sql,int start,int end){
		sql = sql.trim();
		boolean isForUpdate = false;
		if ( sql.toLowerCase().endsWith(" for update") ) {
			sql = sql.substring( 0, sql.length()-11 );
			isForUpdate = true;
		}
		StringBuffer pagingSelect = new StringBuffer( sql.length()+100 );
		pagingSelect.append("select * from ( select row_.*, rownum rownum_ from ( ");
		pagingSelect.append(sql);
		pagingSelect.append(" ) row_ where rownum<="+(end+1)+" ) emps where rownum_ > "+start+"");

		if ( isForUpdate ) {
			pagingSelect.append( " for update" );
		}
		return pagingSelect.toString();
	}
	/*
	 * 组装分页查询语句，该方法只适用于mysql
	 */
	private String getLimitString4MySql(String sql,int start,int end){
		sql = sql.trim();
		boolean isForUpdate = false;
		if ( sql.toLowerCase().endsWith(" for update") ) {
			sql = sql.substring( 0, sql.length()-11 );
			isForUpdate = true;
		}
		StringBuffer pagingSelect = new StringBuffer( sql.length()+100 );
		pagingSelect.append("select * from (  ");
		pagingSelect.append(sql);
		pagingSelect.append(" ) emps limit "+start+","+(end-start));

		if ( isForUpdate ) {
			pagingSelect.append( " for update" );
		}
		return pagingSelect.toString();

	}
	/*
	 * 组装分页查询语句，该方法只适用于HSQL
	 */
	private String getLimitString4HSql(String sql,int start,int end){
		sql = sql.trim();
		boolean isForUpdate = false;
		if ( sql.toLowerCase().endsWith(" for update") ) {
			sql = sql.substring( 0, sql.length()-11 );
			isForUpdate = true;
		}
		StringBuffer pagingSelect = new StringBuffer( sql.length()+20 );
		pagingSelect.append(sql);
		pagingSelect.insert(sql.toLowerCase().indexOf( "select" ) + 6,start>=0? " limit " + start + " " + end : " top "+start);
	
		if ( isForUpdate ) {
			pagingSelect.append( " for update" );
		}
		return pagingSelect.toString();

	}
	private static String getLimitString4SqlServer(String sql,int start,int end){
		sql = sql.trim();		
		int selectIndex=sql.toLowerCase().indexOf("select" )+5;
		int selectEndIndex=sql.toLowerCase().indexOf("from",selectIndex);		
		String selectColumn=sql.toLowerCase().substring(selectIndex+1,selectEndIndex);
		String orderColumnName=StringUtils.split(selectColumn,",")[0];
		boolean isForUpdate = false;
		if ( sql.toLowerCase().endsWith(" for update") ) {
			sql = sql.substring( 0, sql.length()-11 );
			isForUpdate = true;
		}
		StringBuffer pagingSelect = new StringBuffer( sql.length()+100 );
		
		pagingSelect.append(sql);
		pagingSelect.insert(sql.toLowerCase().indexOf( "select" ) + 6," top "+ end );
		pagingSelect.append(sql.toLowerCase().indexOf( "order by" )>0?" ,"+orderColumnName+" asc":" order by "+orderColumnName+" asc" );
		pagingSelect.append(") as TEMPTABLE1 ORDER BY "+orderColumnName+" DESC ");
		pagingSelect.append(") as TEMPTABLE2 ORDER BY "+orderColumnName+" ASC ");
		
		pagingSelect.insert(0,"select top "+(end-start)+" * from (");
		pagingSelect.insert(0,"select * from (");
				
		
		if ( isForUpdate ) {
			pagingSelect.append( " for update" );
		}
		return pagingSelect.toString();
	}
	private String getLimitString4Other(String sql,int start,int end){
		return sql;
	}
	private String addOrder(String sql,Map order){
		if(sql.toLowerCase().indexOf(" order ")>0)
			sql=sql.substring(0,sql.toLowerCase().lastIndexOf(" order "));
		String orderString=" order by";
		Iterator keys=order.keySet().iterator();
		while(keys.hasNext()){
			String key=(String)keys.next();
			String orderValue=(String)order.get(key);
			if(orderValue.equalsIgnoreCase("default")) orderValue="asc";
			if(orderValue.equalsIgnoreCase("asc")||orderValue.equalsIgnoreCase("desc"))
				orderString+=" "+key+" "+orderValue+",";
		}
		if(orderString.endsWith(" order by"))
			return sql;
		return sql+(orderString.endsWith(",")?orderString.substring(0,orderString.length()-1):orderString);
	}

	/**
	 * 将ResultSet转化成map
	 */
	public static Map<String, Object> rs2Map(ResultSet rs) throws SQLException {
		if (null == rs) {
			return null;
		}
		Map<String, Object> map = new HashMap<String, Object>();
		ResultSetMetaData meta = rs.getMetaData();
		int count = meta.getColumnCount();
		for (int i = 0; i < count; i++) {
			map.put(meta.getColumnName(i + 1), rs.getObject(i + 1));
		}
		return map;
	}

	/*
	 * ParameterizedRowMapper接口的简单实现
	 * 按照记录集ResultSet的列名称将列值设置为对应实体bean的属性值
	 * 要使用该实现，应该将查询语句中的字段别名和实体bean中的属性名称对应起来
	 *
	 * @author wuxinyang
	 *
	 */
	class SimpleParameterizedRowMapper implements ParameterizedRowMapper{
		private Class entityClass;

		private ResultSetMetaData rsmd=null;
		private Map<String, String> customerField=new HashMap<String, String>();
		SimpleParameterizedRowMapper(String sql,Class entityClass){
			this.entityClass=entityClass;
			String lowerSql=sql.toLowerCase();
			String select=sql.substring(lowerSql.indexOf("select")+7, lowerSql.indexOf("from")).trim();
			String[] selectColumn=select.split(",");
			for(int i=0;i<selectColumn.length;i++){
				String[] customer= selectColumn[i].split("\\p{Blank}");
				if(customer.length==2){
					customerField.put(customer[1].trim().toUpperCase(),customer[1].trim());
				}
			}
		}
		private Object newEntityInstance(){
			try {
				return Class.forName(entityClass.getName()).newInstance();
			} catch (Exception e) {
				throw new RuntimeException(e);
			}
		}
		private String toFieldName(String columnName){
			if(customerField.get(columnName.toUpperCase())!=null)
				return customerField.get(columnName.toUpperCase());
			String[] word=columnName.split("_");
			StringBuffer fieldName=new StringBuffer(word[0].toLowerCase());
			for(int i=1;i<word.length;i++){
				fieldName.append(word[i].substring(0,1).toUpperCase());
				if(word[i].length()>1)
					fieldName.append(word[i].substring(1).toLowerCase());
			}
			return fieldName.toString();
		}
		/**
		 *
		 */
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			Object entity=newEntityInstance();
			if(rsmd==null){
				rsmd = rs.getMetaData();
			}
			for(int column=1;column<=rsmd.getColumnCount();column++){
				String columnName=rsmd.getColumnName(column);
				String fieldName=toFieldName(columnName);
				try {
					BeanUtils.setDeclaredProperty(entity,fieldName,rs.getObject(columnName));
				} catch (IllegalAccessException e) {

				} catch (NoSuchFieldException e) {

				}
			}
			return entity;
		}
	}
	/*
	 * ParameterizedRowMapper接口的简单实现
	 * 按照记录集ResultSet的列名称将列值设置为Map的键值和值
	 *
	 * @author wuxinyang
	 *
	 */
	class SimpleParameterizedRowToMapsMapper implements ParameterizedRowMapper{
		private ResultSetMetaData rsmd=null;
		private Map<String, String> customerField=new HashMap<String, String>();
		SimpleParameterizedRowToMapsMapper(String sql){			
			String lowerSql=sql.toLowerCase();
			String select=sql.substring(lowerSql.indexOf("select")+7, lowerSql.indexOf("from")).trim();
			String[] selectColumn=select.split(",");
			for(int i=0;i<selectColumn.length;i++){
				String[] customer= selectColumn[i].split("\\p{Blank}");
				if(customer.length==2){
					customerField.put(customer[1].trim().toUpperCase(),customer[1].trim());
				}
			}
		}		
		private String toFieldName(String columnName){			
			if(customerField.get(columnName.toUpperCase())!=null)
				return customerField.get(columnName.toUpperCase());
			String[] word=columnName.split("_");
			StringBuffer fieldName=new StringBuffer(word[0].toLowerCase());
			for(int i=1;i<word.length;i++){
				fieldName.append(word[i].substring(0,1).toUpperCase());
				if(word[i].length()>1)
					fieldName.append(word[i].substring(1).toLowerCase());
			}
			return fieldName.toString();
		}
		/**
		 *
		 */
		public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
			Map map=new HashMap();
			if(rsmd==null){
				rsmd = rs.getMetaData();
			}
			for(int column=1;column<=rsmd.getColumnCount();column++){
				String columnName=rsmd.getColumnName(column);				
				//String fieldName=toFieldName(columnName);
				//20100120修改为使用原始的列名
				map.put(columnName.toUpperCase(), rs.getObject(columnName));				
			}
			return map;
		}
	}
	/* (non-Javadoc)
	 * @see com.witframework.core.persistence.IBaseJdbcDao#setDatabase(java.lang.String)
	 */
	public void setDatabase(String database) {
		this.database = database;
	}
	public static String createFilterQuery(Map filter) {
		if(filter==null||filter.size()==0) return null;
		StringBuffer queryFilter = new StringBuffer();
		Iterator iterator = filter.keySet().iterator();
			while (iterator.hasNext()) {
				String propertyName = (String) iterator.next();
				Object propertyValue = filter.get(propertyName);				
				if (!propertyName.startsWith("s_expression")&&!propertyName.startsWith("_")&&propertyValue != null&&!propertyValue.equals("")){
					queryFilter.append(propertyName +" = "+propertyValue);
				}				
				if (propertyName.startsWith("s_expression")) {
					String pv = ((String) propertyValue).trim();
					String[] exp = StringUtils.split(pv.substring(pv.indexOf("(")+1, pv.length() - 1), ",");
					if (exp.length == 2 || exp.length == 3) {
						String expPropValue=(String)filter.get(exp[1].trim().substring(1));
						propertyName=exp[0].trim();						
						if(!exp[1].startsWith("_"))
								expPropValue = exp[1].trim();
						if(expPropValue!=null&&expPropValue.length()>0){
							propertyName=exp[0].trim();		
							expPropValue=getTypeValue(expPropValue);
							if (pv.startsWith("eq")) {
								queryFilter.append(propertyName +" = "+expPropValue);
							}else if(pv.startsWith("ne")){
								queryFilter.append(propertyName +" != "+expPropValue);
							}else if (pv.startsWith("ge")) {
								queryFilter.append(propertyName +" >= "+expPropValue);
							}else if (pv.startsWith("gt")) {
								queryFilter.append(propertyName +" > "+expPropValue);
							}else if (pv.startsWith("le")) {
								queryFilter.append(propertyName +" < "+expPropValue);
							}else if (pv.startsWith("lt")) {
								queryFilter.append(propertyName +" <= "+expPropValue);
							}else if (pv.startsWith("like")) {								
								if (exp.length == 3) {
									if (exp[2].equals("L")) {
										queryFilter.append(propertyName +" like %"+expPropValue);
									}else if (exp[2].equals("R")) {
										queryFilter.append(propertyName +" like "+expPropValue+"%");
									}else{
										queryFilter.append(propertyName +" like %"+expPropValue+"%");
									}										
								}else
									queryFilter.append(propertyName +" like %"+expPropValue+"%");
								
							}else if (pv.startsWith("between")) {
								if (exp.length == 3) {
									String expPropValue2 = (String) filter.get(exp[2].trim().substring(1));
									expPropValue2=getTypeValue(expPropValue2);
									if (expPropValue2 == null)
										expPropValue2 = exp[2].trim();									
									if(propertyValue!=null)
										queryFilter.append(propertyName +" between "+expPropValue+" and "+expPropValue2);										
								}
							}							
						}
					}
				}
			}
		return queryFilter.toString();
	}
	private static String getTypeValue(String v){
		try{
			Number.class.cast(v);
			return v;
		}catch(Exception e){}
		try{
			java.util.Date.parse(v);
			return "'"+v+"'";			
		}catch(Exception e){}
		return "'"+v+"'";
	}
	
	public static void main(String[] s){
		String sql=BaseJdbcDao.getLimitString4SqlServer("select username from ad_user order by username",1,9);
		System.out.println(sql);
		Map map=new HashMap();map.put("ID", "5");
		map.put("F5","F5");
		map.put("F6","1.8889");
		map.put("F7","2010-01-20 12:01:00.12");
		JdbcTools.insertByWebMap("C_TTTY", map);
	}
}
